﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Backend
{
    /* Group Class 
     * This class will handle all the product groups and the
     * methods to create, update and remove them. */
	class Group
	{
        DBConnect dbconn = new DBConnect();

        public bool Check(int gid)
        {
            int results;
            string query;

            query = dbconn.RunQueryReturn("SELECT COUNT(*) FROM `group` WHERE gid = '" + gid + "'");
            results = Convert.ToInt32(query);

            if (results > 0)
                return true;
            else
                return false;
        }

		public void Create(string groupName, out bool result, out int gid)
		{
			string query;
            int sqlResult;

			query = dbconn.RunQueryReturn("SELECT COUNT(*) FROM `group` WHERE name = '" + groupName + "'");
			sqlResult = Convert.ToInt32(query);

            if (sqlResult > 0)
            {
                result = false;
                gid = 0;
            }
            else
            {
                dbconn.RunQuery("INSERT INTO `group` (name) VALUES ('" + groupName + "')");
                gid = Convert.ToInt32(dbconn.RunQueryReturn("SELECT MAX(gid) FROM `group`"));
                result = true;
            }
		}

		public bool Update(int gid, string groupName)
		{
			if (Check(gid) != true)
				return false;
			else
			{
				dbconn.RunQuery("UPDATE `group` SET name = '" + groupName + "' WHERE gid = '" + gid + "' LIMIT 1");
				return true;
			}
		}

		public bool Remove(int gid)
		{
            if (Check(gid) != true)
                return false;
            else
            {
                dbconn.RunQuery("DELETE FROM `group` WHERE gid = '" + gid + "'");
                return true;
            }
		}

        public DataTable List()
        {
            DataTable groupTable = dbconn.BetterDataTable("SELECT gid, name FROM `group` ORDER BY name", 2);
            return groupTable;
        }
	}

    /* Product Class 
     * This class will handle all the products and the methods
     * to create, update, searh and delete the products. */
    class Product
    {
		DBConnect dbconn = new DBConnect();
        Group group = new Group();

        public void Create(int gid, string productName, string productPrice, string productOffer, int productStock, string productWord, out bool result, out int productID)
		{
            if (group.Check(gid) != true)
            {
                productID = 0;
                result = false;
            }
            else
            {
                dbconn.RunQuery("INSERT INTO product (gid, name, price, offerprice, stock, searchword) VALUES ('" + gid + "', '" + productName + "', '" + productPrice + "', '" + productOffer + "', '" + productStock + "', '" + productWord + "')");
                productID = Convert.ToInt32(dbconn.RunQueryReturn("SELECT MAX(pid) FROM product"));
                result = true;
            }			
		}

		public bool Update(int pid, string productName, string price, string offerPrice, int stock, string searchWord)
		{
            if (Check(pid) != true)
                return false;
            else
            {
				string[] updateSet = new string[5];

				updateSet[0] = "name = '" + productName + "'";
				updateSet[1] = "price = '" + price + "'";
				updateSet[2] = "offerprice = '" + offerPrice + "'";
				updateSet[3] = "stock = '" + stock + "'";
				updateSet[4] = "searchword = '" + searchWord + "'";
				
				dbconn.RunQuery("UPDATE product SET " + string.Join(", ", updateSet) + " WHERE pid = '" + pid + "' LIMIT 1");
				return true;
			}
		}

		public bool Remove(int pid)
		{
			int results;
			string query;

			query = dbconn.RunQueryReturn("SELECT COUNT(*) FROM product WHERE pid = '" + pid + "'");
			results = Convert.ToInt32(query);

			if (results != 1)
				return false;
			else
			{
				dbconn.RunQuery("DELETE FROM product WHERE pid = '" + pid + "'");
				return true;
			}
		}

        public bool Check(int pid)
        {
            int results;
            string query;

            query = dbconn.RunQueryReturn("SELECT COUNT(*) FROM product WHERE pid = '" + pid + "'");
            results = Convert.ToInt32(query);

            if (results > 0)
                return true;
            else
                return false;
        }

        public bool RemoveAll(int gid)
        {
            if (group.Check(gid) != true)
                return false;
            else
            {
                dbconn.RunQuery("DELETE FROM product WHERE gid = '" + gid + "'");
                return true;
            }
        }

        public DataTable List(int gid, string searchWord = "")
        {
            string where;

            if (searchWord != "")
                where = " WHERE gid = '" + gid + "' AND searchword LIKE '%" + searchWord + "'";
            else
                where = " WHERE gid = '" + gid + "'";
            
            DataTable productTable = dbconn.BetterDataTable("SELECT pid, name, price, offerprice, stock, searchword FROM product" + where, 6);
            return productTable;
        }

        public string[] GetInfo(int pid)
        {
            string[] productInfo = dbconn.ReturnArray("SELECT name, price, offerprice, stock, searchword FROM product WHERE pid = '" + pid + "'", 5);

            return productInfo;
        }

    }
}
